package net.kts.attraction.search.sqlite;


import java.util.ArrayList;
import java.util.List;


import net.kts.attraction.search.model.LocationFavorite;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;


public class AttractionFavoriteDaoSQLite extends SQLiteOpenHelper {

	public AttractionFavoriteDaoSQLite(Context context, String name,
			CursorFactory factory, int version) {
		super(context, name, factory, version);
		// TODO Auto-generated constructor stub
	}

	@Override
	public void onCreate(SQLiteDatabase arg0) {
		// TODO Auto-generated method stub

//		String sql = "";
//		
//		sql += " CREATE TABLE TBL_LOCATION_FAVORITE ( ";
//		sql += " LOCATION_NAME CHAR, "; 
//		sql += " LATITUDE CHAR, "; 
//		sql += " LONGITUDE CHAR,  ";
//		sql += " REGIST_DATE CHAR,  ";
//		sql += " COMMENT CHAR,  ";
//		sql += " LOCATION_NAME_KR CHAR); ";
//		
//		getWritableDatabase().rawQuery(sql, null);
		
	}

	@Override
	public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) {
		// TODO Auto-generated method stub

	}
	
	public void insertFavorite(LocationFavorite	favorite) {
//		location.get
		
		
		String sql = "";
		
		sql += " insert into TBL_LOCATION_FAVORITE ";
		sql += " (LOCATION_NAME, LOCATION_NAME_KR, LATITUDE, LONGITUDE, REGIST_DATE, COMMENT) ";
		sql += " VALUES( ";
		sql += " \"" + favorite.getLocationName() + "\" , \""+ favorite.getLocationNameKr()+"\" , "+ favorite.getLatitude() +", "+favorite.getLongitude()+", date('now'), \""+ favorite.getComment()+"\" ";
		sql += " ) ";
		
//		String[] args = new String[] {
//			favorite.getLocationName(),
//			favorite.getLocationNameKr(),
//			String.valueOf(favorite.getLatitude()),
//			String.valueOf(favorite.getLongitude()),
//			favorite.getComment()
//		};
		
//		sql += "insert into TBL_LOCATION_FAVORITE (LOCATION_NAME, LOCATION_NAME_KR, LATITUDE, LONGITUDE, REGIST_DATE, COMMENT)  VALUES(  \"서울특별시 강남구 논현동\" , \"Nonhyeon-Dong, Gangnam-gu, Seoul\" , 37.513812, 127.031673, date('now'), \"\"  )";
		
//		getWritableDatabase().in
		
//		ContentValues values = new ContentValues();
//		
//		values.put("LOCATION_NAME", favorite.getLocationName());
//		values.put("LOCATION_NAME_KR", favorite.getLocationNameKr());
//		values.put("LATITUDE", favorite.getLatitude());
//		values.put("LONGITUDE", favorite.getLongitude());
//		values.put("REGIST_DATE", "date('now')");
//		values.put("COMMENT", favorite.getComment());
//		
////		Log.d("kts", "insert favorite data : " + sql);
//		long l = getWritableDatabase().insert("TBL_LOCATION_FAVORITE", null, values);
//		Log.d("kts", "insert favorite data : " + l);
////		getWritableDatabase().rawQuery(sql, null);
		
//		getWritableDatabase().execSQL(sql);
		
		getWritableDatabase().beginTransaction();
		try {
			getWritableDatabase().execSQL(sql);
			getWritableDatabase().setTransactionSuccessful();
		} catch (SQLException e) {
            
        }finally{
        	getWritableDatabase().endTransaction();
        }		
		
		
//		'SEXY', '섹시', 37.111, 127.222, date('now'), 'no comment')
		
	}
	
	public List<LocationFavorite> selectFavoriteList() {
		
		List<LocationFavorite> favoriteList = null;
		
//		SimpleDateFormat formatter = new SimpleDateFormat ( "yyyy-MM-dd");
		
		
		String sql = "select rowid, LOCATION_NAME, LOCATION_NAME_KR, LATITUDE, LONGITUDE, REGIST_DATE, COMMENT from TBL_LOCATION_FAVORITE";
		
		Cursor cursor = getReadableDatabase().rawQuery(sql, null);
		
		cursor.moveToFirst();
		
		
		LocationFavorite favorite = null;
		while(!cursor.isAfterLast()) {
			
			if(favoriteList == null) {
				favoriteList = new ArrayList<LocationFavorite>();
			}
			
			favorite = new LocationFavorite();
			
			favorite.setSeq(cursor.getInt(0));
			favorite.setLocationName(cursor.getString(1));
			favorite.setLocationNameKr(cursor.getString(2));
			favorite.setLatitude(cursor.getDouble(3));
			favorite.setLongitude(cursor.getDouble(4));
			favorite.setRegistDate(cursor.getString(5));
			favorite.setComment(cursor.getString(6));
			
			
			favoriteList.add(favorite);
			
			cursor.moveToNext();
		}
		
		
		
		return favoriteList;
		
	}
	
	public void deleteFavorite(LocationFavorite favorite) {
		
		String sql = "";
		sql += "delete from TBL_LOCATION_FAVORITE ";
		sql += " where rowid = " +favorite.getSeq();
		
		getWritableDatabase().beginTransaction();
		try {
			getWritableDatabase().execSQL(sql);
			getWritableDatabase().setTransactionSuccessful();
		} catch (SQLException e) {
            
        }finally{
        	getWritableDatabase().endTransaction();
        }		
		
	}

}
